package cn.iocoder.yudao.module.ao.service.labschedule;

import cn.iocoder.yudao.module.ao.controller.admin.labschedule.vo.LabScheduleImportExcelVO;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import cn.hutool.core.util.StrUtil;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 教学场所课程表Excel解析器
 * 用于解析复杂的课程表格式，包含多个连续表格
 * 支持机房、实训室、舞蹈室、茶艺室、画室、电工室等各种教学场所
 */
@Slf4j
@Component
public class LabScheduleExcelParser {

    // 学期信息正则表达式，匹配如"2024-2025学年 第二学期 1-28周"
    private static final Pattern TERM_PATTERN = Pattern.compile("(\\d{4}-\\d{4}学年\\s+第[一二三四]学期\\s+\\d+-\\d+周)");
    
    // 教学场所名称正则表达式，匹配各种类型的教学场所
    private static final Pattern LAB_NAME_PATTERN = Pattern.compile("([^\\s]+校区-[^\\s]+-[^\\s]+(机房|实训室|舞蹈室|茶艺室|画室|电工室?)\\d*)");

    // 教学场所类型关键词
    private static final String[] PLACE_KEYWORDS = {"机房", "实训室", "舞蹈室", "茶艺室", "画室", "电工室", "电工"};

    /**
     * 解析Excel文件
     */
    public List<LabScheduleImportExcelVO> parseExcel(InputStream inputStream, String sheetName) throws Exception {
        log.info("开始解析Excel文件，工作表名称: {}", sheetName);
        List<LabScheduleImportExcelVO> result = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            log.info("成功创建工作簿，工作表数量: {}", workbook.getNumberOfSheets());

            Sheet sheet;
            if (StringUtils.hasText(sheetName)) {
                sheet = workbook.getSheet(sheetName);
                log.info("根据名称获取工作表: {}, 结果: {}", sheetName, sheet != null ? "成功" : "失败");
            } else {
                sheet = workbook.getSheetAt(0);
                log.info("获取第一个工作表: {}", sheet != null ? sheet.getSheetName() : "null");
            }

            if (sheet == null) {
                log.warn("工作表为空，返回空结果");
                return result;
            }

            log.info("开始解析工作表: {}, 行数: {}", sheet.getSheetName(), sheet.getLastRowNum() + 1);

            // 解析表格
            List<TableInfo> tables = parseTablesFromSheet(sheet);
            log.info("解析到 {} 个表格", tables.size());

            // 将每个表格转换为导入VO
            for (TableInfo table : tables) {
                List<LabScheduleImportExcelVO> tableData = convertTableToVOs(table);
                log.info("表格转换完成，生成 {} 条数据", tableData.size());
                result.addAll(tableData);
            }
        }

        log.info("Excel解析完成，总共生成 {} 条数据", result.size());
        return result;
    }
    
    /**
     * 从工作表中解析出所有表格
     */
    private List<TableInfo> parseTablesFromSheet(Sheet sheet) {
        List<TableInfo> tables = new ArrayList<>();
        String currentLabName = ""; // 记录当前的教学场所名称

        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;

            // 检查是否包含教学场所名称信息
            String rowText = getRowText(row);
            if (containsPlaceKeyword(rowText)) {
                String extractedLabName = extractLabNameFromTitle(rowText);
                if (StringUtils.hasText(extractedLabName)) {
                    currentLabName = extractedLabName;
                    log.info("在第{}行发现教学场所名称: {}", rowIndex + 1, currentLabName);
                }
            }

            // 检查是否是表格标题行
            String firstCellValue = getCellStringValue(row.getCell(0));
            if (StringUtils.hasText(firstCellValue)) {
                Matcher termMatcher = TERM_PATTERN.matcher(firstCellValue);
                if (termMatcher.find()) {
                    // 找到学期信息，开始解析表格
                    TableInfo table = parseTable(sheet, rowIndex);
                    if (table != null) {
                        // 如果表格中没有教学场所名称，使用当前记录的教学场所名称
                        if (!StringUtils.hasText(table.labName) && StringUtils.hasText(currentLabName)) {
                            table.labName = currentLabName;
                            log.info("为表格分配教学场所名称: {}", currentLabName);
                        }
                        tables.add(table);
                        rowIndex = table.endRow; // 跳过已解析的行
                    }
                }
            }
        }

        return tables;
    }

    /**
     * 获取整行的文本内容
     */
    private String getRowText(Row row) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < Math.min(10, row.getLastCellNum()); i++) {
            String cellValue = getCellStringValue(row.getCell(i));
            if (StringUtils.hasText(cellValue)) {
                sb.append(cellValue).append(" ");
            }
        }
        return sb.toString().trim();
    }
    
    /**
     * 解析单个表格
     */
    private TableInfo parseTable(Sheet sheet, int startRow) {
        Row titleRow = sheet.getRow(startRow);
        if (titleRow == null) return null;

        String titleText = getCellStringValue(titleRow.getCell(0));

        // 提取学期信息
        Matcher termMatcher = TERM_PATTERN.matcher(titleText);
        String term = termMatcher.find() ? termMatcher.group(1) : "";

        // 提取教学场所名称 - 更全面的搜索策略
        String labName = extractLabNameFromTitle(titleText);

        // 如果在标题行没找到，搜索前后几行
        if (labName.isEmpty()) {
            labName = searchLabNameInNearbyRows(sheet, startRow);
        }

        // 查找表格的数据开始行（包含"节次"的行）
        int dataStartRow = findDataStartRow(sheet, startRow);
        if (dataStartRow == -1) return null;

        // 查找表格的结束行
        int dataEndRow = findDataEndRow(sheet, dataStartRow);

        TableInfo table = new TableInfo();
        table.term = term;
        table.labName = labName;
        table.startRow = dataStartRow;
        table.endRow = dataEndRow;
        table.sheet = sheet;

        log.info("解析表格 - 学期: {}, 教学场所: {}, 数据行: {}-{}", term, labName, dataStartRow, dataEndRow);

        return table;
    }

    /**
     * 在附近的行中搜索教学场所名称
     */
    private String searchLabNameInNearbyRows(Sheet sheet, int centerRow) {
        // 搜索前后5行
        for (int offset = -2; offset <= 5; offset++) {
            int rowIndex = centerRow + offset;
            if (rowIndex < 0 || rowIndex > sheet.getLastRowNum()) continue;

            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;

            // 检查该行的所有单元格
            for (int cellIndex = 0; cellIndex < Math.min(10, row.getLastCellNum()); cellIndex++) {
                String cellValue = getCellStringValue(row.getCell(cellIndex));
                if (StringUtils.hasText(cellValue) && containsPlaceKeyword(cellValue)) {
                    // 进一步验证是否是教学场所名称
                    if (cellValue.contains("校区") || cellValue.contains("-") ||
                        cellValue.matches(".*(机房|实训室|舞蹈室|茶艺室|画室|电工室?)\\d*.*")) {
                        return cellValue;
                    }
                }
            }
        }
        return "";
    }
    
    /**
     * 从标题中提取教学场所名称
     */
    private String extractLabNameFromTitle(String titleText) {
        if (!StringUtils.hasText(titleText)) {
            return "";
        }

        // 先尝试正则匹配
        Matcher labMatcher = LAB_NAME_PATTERN.matcher(titleText);
        if (labMatcher.find()) {
            return labMatcher.group(1);
        }

        // 尝试匹配更宽松的模式
        if (containsPlaceKeyword(titleText)) {
            // 查找包含教学场所关键词的连续字符串
            String[] parts = titleText.split("\\s+");
            for (String part : parts) {
                if (containsPlaceKeyword(part)) {
                    // 如果包含校区信息，优先选择
                    if (part.contains("校区") || part.contains("-")) {
                        return part;
                    }
                }
            }

            // 如果没有找到包含校区的，返回第一个包含教学场所关键词的部分
            for (String part : parts) {
                if (containsPlaceKeyword(part)) {
                    return part;
                }
            }

            // 最后尝试从整个字符串中提取
            for (String keyword : PLACE_KEYWORDS) {
                if (titleText.contains(keyword)) {
                    // 使用正则提取包含该关键词的词
                    Pattern pattern = Pattern.compile("([^\\s]*" + keyword + "[^\\s]*)");
                    Matcher matcher = pattern.matcher(titleText);
                    if (matcher.find()) {
                        return matcher.group(1);
                    }
                }
            }
        }

        return "";
    }
    
    /**
     * 查找数据开始行（包含"节次"的行）
     */
    private int findDataStartRow(Sheet sheet, int startRow) {
        for (int i = startRow; i <= Math.min(startRow + 10, sheet.getLastRowNum()); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                String firstCell = getCellStringValue(row.getCell(0));
                if ("节次".equals(firstCell) || firstCell.contains("节次")) {
                    return i + 1; // 返回数据行，而不是标题行
                }
            }
        }
        return -1;
    }
    
    /**
     * 查找数据结束行
     */
    private int findDataEndRow(Sheet sheet, int dataStartRow) {
        for (int i = dataStartRow; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                return i - 1;
            }
            
            String firstCell = getCellStringValue(row.getCell(0));
            // 如果遇到空行或新的学期信息，表示表格结束
            if (!StringUtils.hasText(firstCell) || TERM_PATTERN.matcher(firstCell).find()) {
                return i - 1;
            }
        }
        return sheet.getLastRowNum();
    }
    
    /**
     * 将表格转换为VO对象
     */
    private List<LabScheduleImportExcelVO> convertTableToVOs(TableInfo table) {
        List<LabScheduleImportExcelVO> result = new ArrayList<>();
        
        // 按节次分组数据
        Map<String, LabScheduleImportExcelVO> sectionMap = new HashMap<>();
        
        for (int rowIndex = table.startRow; rowIndex <= table.endRow; rowIndex++) {
            Row row = table.sheet.getRow(rowIndex);
            if (row == null) continue;
            
            String classSection = getCellStringValue(row.getCell(0));
            if (!StringUtils.hasText(classSection)) continue;
            
            // 处理时间信息（如果第二列是时间）
            String timeInfo = getCellStringValue(row.getCell(1));
            if (StringUtils.hasText(timeInfo) && timeInfo.contains(":")) {
                classSection = classSection + " " + timeInfo;
            }
            
            LabScheduleImportExcelVO vo = sectionMap.get(classSection);
            if (vo == null) {
                vo = new LabScheduleImportExcelVO();
                vo.setTerm(table.term);
                vo.setLabName(table.labName);
                vo.setClassSection(classSection);
                sectionMap.put(classSection, vo);
            }
            
            // 解析周一到周五的数据
            int startColumn = timeInfo.contains(":") ? 2 : 1;
            
            if (startColumn + 0 < row.getLastCellNum()) {
                String mondayUsage = getCellStringValue(row.getCell(startColumn + 0));
                if (StringUtils.hasText(mondayUsage) && !mondayUsage.equals("空")) {
                    vo.setMondayUsage(mondayUsage);
                }
            }
            
            if (startColumn + 1 < row.getLastCellNum()) {
                String tuesdayUsage = getCellStringValue(row.getCell(startColumn + 1));
                if (StringUtils.hasText(tuesdayUsage) && !tuesdayUsage.equals("空")) {
                    vo.setTuesdayUsage(tuesdayUsage);
                }
            }
            
            if (startColumn + 2 < row.getLastCellNum()) {
                String wednesdayUsage = getCellStringValue(row.getCell(startColumn + 2));
                if (StringUtils.hasText(wednesdayUsage) && !wednesdayUsage.equals("空")) {
                    vo.setWednesdayUsage(wednesdayUsage);
                }
            }
            
            if (startColumn + 3 < row.getLastCellNum()) {
                String thursdayUsage = getCellStringValue(row.getCell(startColumn + 3));
                if (StringUtils.hasText(thursdayUsage) && !thursdayUsage.equals("空")) {
                    vo.setThursdayUsage(thursdayUsage);
                }
            }
            
            if (startColumn + 4 < row.getLastCellNum()) {
                String fridayUsage = getCellStringValue(row.getCell(startColumn + 4));
                if (StringUtils.hasText(fridayUsage) && !fridayUsage.equals("空")) {
                    vo.setFridayUsage(fridayUsage);
                }
            }
        }
        
        // 过滤掉无效数据，只保留有效的记录
        for (LabScheduleImportExcelVO vo : sectionMap.values()) {
            if (isValidLabScheduleData(vo)) {
                result.add(vo);
                log.debug("添加有效数据: 学期={}, 教学场所={}, 节次={}", vo.getTerm(), vo.getLabName(), vo.getClassSection());
            } else {
                log.warn("过滤无效数据: 学期={}, 教学场所={}, 节次={}", vo.getTerm(), vo.getLabName(), vo.getClassSection());
            }
        }

        return result;
    }
    
    /**
     * 检查文本是否包含教学场所关键词
     */
    private boolean containsPlaceKeyword(String text) {
        if (!StringUtils.hasText(text)) {
            return false;
        }

        for (String keyword : PLACE_KEYWORDS) {
            if (text.contains(keyword)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 验证课程表数据是否有效
     */
    private boolean isValidLabScheduleData(LabScheduleImportExcelVO vo) {
        // 检查必要字段是否为空
        if (StrUtil.isBlank(vo.getTerm()) ||
            StrUtil.isBlank(vo.getLabName()) ||
            StrUtil.isBlank(vo.getClassSection())) {
            return false;
        }

        // 检查是否至少有一个使用信息
        boolean hasUsage = StrUtil.isNotBlank(vo.getMondayUsage()) ||
                          StrUtil.isNotBlank(vo.getTuesdayUsage()) ||
                          StrUtil.isNotBlank(vo.getWednesdayUsage()) ||
                          StrUtil.isNotBlank(vo.getThursdayUsage()) ||
                          StrUtil.isNotBlank(vo.getFridayUsage());

        return hasUsage;
    }

    /**
     * 获取单元格字符串值
     */
    private String getCellStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue().trim();
                } catch (Exception e) {
                    return String.valueOf(cell.getNumericCellValue());
                }
            default:
                return "";
        }
    }
    
    /**
     * 表格信息类
     */
    private static class TableInfo {
        String term;
        String labName;
        int startRow;
        int endRow;
        Sheet sheet;
    }
}
